package com.aluem.mydataxwebexecutor.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.aluem.mydataxwebexecutor.bean.TableMsg;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

@Component
public class JDBCUtils {

    private static final String SQL = "SELECT * FROM ";// 数据库操作
    private static final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver"; //mysql驱动
    private static final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver"; //oracle驱动

    /**
     * 测试修改list
     */
//    public static void main(String[] args) {
//        List<String> list = new ArrayList<>();
//        list.add("0");
//        list.add("1");
//        list.add("2");
//        update(list);
//        System.out.println(list); //输出0 2 2 :because  List是引用类型数据
//    }
//
//    public static void update(List<String> list) {
//        for (int i = 0; i < list.size(); i++) {
//            if (list.get(i).equals("1")) {
//                list.set(i, "2");
//            }
//        }
//    }
    

    //获取数据库连接
    public static Connection getConnection(TableMsg tableMsg) throws Exception {
        Properties pros = new Properties();
        //设计数据源参数
        pros.setProperty("url", tableMsg.getJdbcUrl());
        pros.setProperty("username", tableMsg.getUsername());

        if ("mysql".equalsIgnoreCase(tableMsg.getDatabase())) { //若要获取mysql连接
            pros.setProperty("driverClassName", MYSQL_DRIVER);
        } else if ("oracle".equalsIgnoreCase(tableMsg.getDatabase())) { //若要获取oracle连接
            pros.setProperty("driverClassName",ORACLE_DRIVER);
        }
        pros.setProperty("password", tableMsg.getPassword());

        DataSource source = DruidDataSourceFactory.createDataSource(pros);

        //获取数据库连接
        return  source.getConnection();

    }

    /**
     * 获取表中所有字段名称
     * @param tableName 表名
     * @return
     */
    public static List<String> getColumnNames(String tableName,Connection conn) {
        List<String> columnNames = new ArrayList<>();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeResource(null, pStemt, null);
                } catch (SQLException e) {
                }
            }
        }
        return columnNames;
    }



    /**
     * 获取表中所有字段类型
     */
    public static List<String> getColumnTypes(String tableName,Connection conn) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        PreparedStatement pStemt = null;
        ResultSetMetaData rsmd = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
            return columnTypes;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeResource(null,pStemt,null);
                } catch (SQLException e) {
                }
            }
        }
        return null;
    }

    /**
     * 获取表中所有字段长度
     * @param tableName
     * @return
     */
    public static List<Integer> getColumnLength(String tableName,Connection conn) {
        List<Integer> columnLength = new ArrayList<>();
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnLength.add(rsmd.getColumnDisplaySize(i + 1));
            }
            return columnLength;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                    closeResource(null, pStemt, null);
                } catch (SQLException e) {
                }
            }
        }
        return null;
    }

    //执行sql语句 增删改
    public static int update(Connection conn, String sql, Object... args) {// sql中占位符的个数与可变形参的长度相同！
        PreparedStatement ps = null;
        try {
            // 1.预编译sql语句，返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            // 2.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);// 小心参数声明错误！！
            }
            // 3.执行
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 4.资源的关闭
            JDBCUtils.closeResource(null, ps, null);

        }
        return 0;

    }


    //用于查询特殊值的通用的方法
    public static  <E> E getValue(Connection conn,String sql,Object...args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for(int i = 0;i < args.length;i++){
                ps.setObject(i + 1, args[i]);

            }

            rs = ps.executeQuery();
            if(rs.next()){
                return (E) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.closeResource(null, ps, rs);

        }
        return null;

    }

    /**
     * @Description 关闭资源操作
     */
    public static void closeResource(Connection conn, Statement ps, ResultSet rs){
        try {
            if(ps != null)
                ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}




